#
# This aggregator queries both melissadata and networkforgood/guidestar for information about non-profits:
#
#   http://w5.melissadata.com/lookups/np.asp?zip=97048&submit1=Submit
#   http://melissadata.com/lookups/np.asp?ein=131659627
#   http://www.guidestar.org/pqShowGsReport.do?partner=networkforgood&ein=13-1659627
#


require 'cgi'
require 'rubygems'
require 'hpricot'
require 'open-uri'
require 'uri'
require 'net/http'
require_gem 'activerecord'

#
# Establish connection to database
#

ActiveRecord::Base.establish_connection({
  :adapter => "postgresql",
  :database => "yg",
  :username => "yg",
  :password => "yg",
  :host => "127.0.0.1",
  :port => 5432
})
ActiveRecord::Base.logger = Logger.new("test.log");


# quote
#

def q(term)
  return "\"#{term}\""
end

#
# A non profit
#

class Nonprofit < ActiveRecord::Base 
end

#
# A class to organize some of our aggregator utilities
#

class Aggregator
  #
  # melissa will deny access unless we can forge a cookie for it
  #
  # Query melissa for EIN's in a range
  # Search result page for <a> tags that have the phrase 'ein' in them.
  # Store that ein if not present
  #
  def melissa_begin(start,theend)
    h = Net::HTTP.new("melissadata.com",80)
    resp,data = h.get('/lookups/np.asp',nil)
    #puts "Message = #{resp.message}"
    #resp.each {|key, val| printf "%-14s = %-40.40s\n", key, val }
    #p data[0..55]
    headers = {
      'Cookie' => resp.response['set-cookIe'],
      'Referer' => 'http://melissadata.com/lookups/np.asp',
      'Content-Type' => 'application/x-www-form-urlencoded'
    }

    (start..theend).each do |i|
      puts "Melissa: visiting zipcode #{i}"
      resp, data = h.get("/lookups/np.asp?zip=#{i}",headers)
      doc = Hpricot(data)
      (doc/:a).each do |a|
        href = a.attributes['href']
        name = a.inner_html
        ein = href.split(/ein\=/)[1]
        unless ein == nil || Nonprofit.find(:first, :conditions => "ein = #{ein}" )
          Nonprofit.create(:ein => ein, :name => name, :zipcode => i ).save!
          puts "Melissa: Stored new organization: ein=#{ein} name=#{name}"
        end
      end
    end
  end

  #
  # revisit an individual ein from guidestar
  #
  def guidestar_visit(np)

    # clean up ein
    ein = "#{np.ein.to_s}"
    while ein.length < 9
      ein = "0#{ein}"
    end
    ein = "#{ein[0..1]}-#{ein[2..9]}"
    #ein = "33-0519225"

    url = "/pqShowGsReport.do?partner=networkforgood&ein=#{ein}"
    puts "Guidestar visiting #{np.name} at #{url}"

    # get the content
    resp,data = @h.get(url,nil)
    doc = Hpricot(data)

    # error check for valid doc
    if !doc then next; end
    nott = (doc/:h1).first
    if !nott then next; end
    nott = nott.inner_html
    if nott == "Nonprofit Not Found"
      puts "Non profit not found!"
      next
    end

    # get the name of the np
    name = (doc/:h1).first.inner_html.strip
    if name.length < 6 
      puts "name #{name} seems unusually short"
      return
    end

    puts "[#{name}]"
    if name == 'Found'
      loc = (doc/:a).first
      href = loc.attributes['href']
      href = href[24..999]
      href = CGI::unescapeHTML(href)
      puts "Non profit appears to have moved to #{href}"
      resp,data = @h.get(href,nil)
      doc = Hpricot(data)
      return
    end

    # get the ntee codes
    ntee = nil
    (doc/:ul/:li).each do |term|
       term.inner_html.scan(/[A-Z][0-9][0-9]/) do |t|
         ntee = "#{ntee}," if ntee
         ntee = "#{ntee}#{t}"
       end
    end

    # try get address
    if (doc/:address).first
      addr = (doc/:address).first.inner_html.strip
      addr =addr.gsub(/[\n\r]/,' ').gsub(/<br \/>/,',').squeeze(' ,').squeeze(' ');
    end

    # try get the site url
    site = (doc/"p.noMargin/a").first
    site = site.inner_html if site

    # try get a mission statement
    miss = (doc/:h3).first
    if miss && miss.inner_html == "Mission"
      temp = (doc/:p)
      if temp && temp.length 
        miss = temp[0].inner_html
        miss = temp[2].inner_html if miss && miss[0..9] == "Also Known"
        miss = miss.gsub(/[\n\r]/,' ').strip if miss
      end
    else
      miss = nil
    end

    # try get a whole raft of neat little facts
    asse = nil 
    inco = nil
    xexec = nil 
    cont = nil
    foun = nil
    (doc/"ul.noBullets/li/h3").each do |lih3a|
       foun = (lih3a/"../p").first.inner_html if lih3a.inner_html == "Year Founded"
       xexec = (lih3a/"../p").first.inner_html if lih3a.inner_html == "Executive"
       cont = (lih3a/"../p").first.inner_html if lih3a.inner_html == "Contact"
    end
    (doc/"ul.noBullets/li/h3/a").each do |lih3a|
       asse = (lih3a/"../../p").first.inner_html if lih3a.inner_html == "Assets"
       inco = (lih3a/"../../p").first.inner_html if lih3a.inner_html == "Income"
    end
 
    # do some cleanup on bad layout
    xexec = xexec.gsub(/[\n\r]/,' ').squeeze(" ").strip if xexec
    cont = cont.gsub(/[\n\r]/,' ').squeeze(" ").strip if cont 
    foun = foun.gsub(/[\n\r]/,' ').squeeze(" ").strip if foun 
    foun = nil if foun == "Information not available"
    asse = asse.split(" ")[0] if asse 
    inco = inco.split(" ")[0] if inco

    # print for my own review 
    puts "------------------------------------"
    puts "NAME: #{name}."
    puts "ADDR: #{addr}."
    puts "SITE: #{site}."
    puts "NTEE: #{ntee}."
    puts "MISS: #{miss}."
    puts "ASSE: #{asse}."
    puts "INCO: #{inco}."
    puts "EXEC: #{xexec}."
    puts "CONT: #{cont}."
    puts "FOUN: #{foun}."

    # save it

    np.address = addr if addr
    np.ntee = ntee
    np.site = site if site
    np.mission = miss if miss
    np.assets = asse if asse
    np.income = inco if inco
    np.executive = xexec if xexec
    np.contact = cont if cont
    np.founded = foun if foun
    np.modified_at = Time.now
    np.created_at = Time.now if !np.created_at
    np.save

  end

  #
  # Query guidestar for more details about non-profits
  #
  def guidestar_begin(start,theend)

    @h = Net::HTTP.new("www.guidestar.org",80)

    puts "Guidestar: Looking between #{start} and #{theend}"

    # Get some data to look at
    begin
      nps = Nonprofit.find_by_sql("SELECT id,ein,zipcode from nonprofits where zipcode between #{start} and #{theend} order by RANDOM()")
    rescue
      puts "Stopping this thread due to pgsql failure"
      #Thread.current.stop if Thread.current
      return
    end
    puts "***********************************************************"
    puts "Guidestar: we found #{nps.length} candidates valid in range:"
    if nil
      nps.each do |np|
        np = Nonprofit.find_by_ein(np.ein)
        puts "Guidestar: #{np.ein} #{np.name} #{np.address}"
      end
    end

    # Look in our database for any unfulfilled non-profit
    # Get in random order so we reduce thread contention and repeat visits
    begin
      nps = Nonprofit.find_by_sql("SELECT id,ein,zipcode from nonprofits where zipcode between #{start} and #{theend} and ( address is NULL OR char_length(address) < 3) order by RANDOM()")
    rescue
      puts "Stopping this thread due to pgsql failure"
      #Thread.current.stop if Thread.current
      return
    end
    puts "***********************************************************"
    puts "Guidestar: we found #{nps.length} candidates to ask about"

    nps.each do |np|

      np = Nonprofit.find_by_ein(np.ein)

      # skip entries i already did some analysis of
      if !np || np.address != nil && np.address.length > 0
        puts "#{np.name} was already visited - skipping"
        next
      end

      # get more details of those already visited
      begin
        guidestar_visit(np)
      rescue
        puts "failed"
      end

    end

  end

  def emit(start,theend)
    nps = Nonprofit.find_by_sql("SELECT * from nonprofits where zipcode between #{start} and #{theend} and char_length(address) > 3 order by ein")
    f = File.new("np#{start}.csv","w");
    f.puts "id,district_id,name,ntee,address,address2,city,state,zipcode,phone,hours,site,discountoffer,data_source_id,password,email,contact,last_name,longitude,latitude,ein,mission,assets,income,executive,founded,import_code,yp_approved"
    nps.each do |np|
      id = np.id
      district_id = ''
      name = q np.name
      ntee = q np.ntee
      address = q np.address
      address2 = ''
      city = ''
      state = ''
      zipcode = q np.zipcode
      phone = ''
      hours = ''
      site = q np.site
      discountoffer = ''
      data_source_id = ''
      password = ''
      email = ''
      contact = q np.contact
      last_name = ''
      longitude = ''
      latitude = ''
      ein = q np.ein
      mission = q np.mission
      assets = q np.assets
      income = q np.income
      executive = q np.executive
      founded = q np.founded
      import_code = 1
      f.puts "#{id},#{district_id},#{name},#{ntee},#{address},#{address2},#{city},#{state},#{zipcode},#{phone},#{hours},#{site},#{discountoffer},#{data_source_id},#{password},#{email},#{contact},#{last_name},#{longitude},#{latitude},#{ein},#{mission},#{assets},#{income},#{executive},#{founded},#{import_code},0"
    end
    f.close
  end

end

#
# fire off multiple threads here (actually we just do this in unix)
#

#threads = []
#threadcount = 10
#threadcount.times do |tnum|
# puts "making thread #{tnum}"
# threads << Thread.new(tnum) {
#  lstart = start + (tend-start)/threadcount * tnum 
#  lend = lstart + (tend-start)/threadcount
# }
#end
#threads.each { |t| t.join }
#

#
# Drive the system
#

start = ARGV[0].to_i
theend = ARGV[1].to_i
cmd = ARGV[2]
if start == nil || theend == nil
  puts "supply start and end"
  exit
end

aggregator = Aggregator.new
if cmd == 'emit'
  aggregator.emit(start,theend)
elsif cmd == 'melissa'
  aggregator.melissa_begin( start, theend )
elsif cmd == 'guidestar'
  aggregator.guidestar_begin( start, theend )
end


